﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data.SqlTypes;
using NyBio.Model;

namespace NyBio.DB
{
    public class ScheduleDB
    {
        private Connection connection;
        private MovieDB movieDB;
        private RoomDB roomDB;

        public ScheduleDB()
        {
            connection = new Connection();
            movieDB = new MovieDB();
            roomDB = new RoomDB();
        }

        public List<Schedule> getSchedules()
        {
            return where("");
        }

        public Schedule getScheduleById(int id)
        {
            return singleWhere("xpSchedule_id = " + id);
        }

        public List<Schedule> getScheduleByMovieId(int id)
        {
            return where("xpMovie_id = " + id);
        }

        private Schedule singleWhere(String wClause)
        {
            Schedule schedule = null;

            string query = "SELECT * FROM XpSchedule WHERE " + wClause;
            connection.Open();
            SqlDataReader reader = connection.Read(query);

            if (reader.Read())
            {
                schedule = new Schedule();
                schedule._id = reader.GetInt32(0);
                schedule._time = reader.GetDateTime(1);
                schedule._movieId = reader.GetInt32(2);
                schedule._roomId = reader.GetInt32(3);
                schedule._price = reader.GetDecimal(4);
            }

            reader.Close();
            connection.Close();

            return schedule;
        }

        private List<Schedule> where(String wClause)
        {
            List<Schedule> schedules = new List<Schedule>();
            string query = "SELECT * FROM XpSchedule WHERE " + wClause;
            connection.Open();
            SqlDataReader reader = connection.Read(query);
            while (reader.Read())
            {
                Schedule schedule = new Schedule();
                schedule._id = reader.GetInt32(0);
                schedule._time = reader.GetDateTime(1);
                schedule._price = reader.GetDecimal(2);
                schedule._movieId = reader.GetInt32(3);
                schedule._roomId = reader.GetInt32(4);
                
                schedules.Add(schedule);
            }

            reader.Close();
            connection.Close();

            return schedules;
        }

        public void removeSchedule(int id)
        {
            string query = "DELETE FROM XpSchedule WHERE xpSchedule_id=" + id;
            connection.Open();
            connection.Execute(query);
            connection.Close();

        }

        public void insertSchedule(Schedule schedule)
        {
            string query = @"INSERT INTO XpSchedule (time, movieId, roomId, price) " +
                "VALUES ( " + schedule._time + ", " + schedule._price
                + ", " + schedule._movieId + " , " + schedule._roomId + " , " + schedule._price  +")";
            connection.Open();
            connection.Execute(query);
            connection.Close();
        }

        public void updateSchedule(Schedule schedule)
        {
            string query = @"UPDATE XpSchedule SET time=" + schedule._time +
                ", price=" + schedule._price + ", xpMovie_id=" + schedule._movieId + ", xpRoom_id=" + schedule._roomId +
                " WHERE xpSchedule_id=" + schedule._id;
            connection.Open();
            connection.Execute(query);
            connection.Close();

        }
        public List<Schedule> getScheduleByMovieTitle(string title)
        {

            return where("title = " + title);
        }
        
        
    }
}